package done;

import houseHold.House;
import houseHold.HouseHoldObject;
import houseHold.Room;

import java.io.File;
import java.sql.Connection;
import java.util.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;
import java.util.Set;

import person.Person;
import person.PersonAchive;
import sensor.Sensor;
import toXML.LoadFamilyXML;
import toXML.LoadHouseXML;

public class DBConnection {
	public static Connection conn = null;

	public static void initConnection() {
		conn = null;
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "test";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "";
		try {
			Class.forName(driver).newInstance();
			conn = DriverManager
					.getConnection(url + dbName, userName, password);
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void closeConn() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private static String data="SimulationData_",sensor="SensorData_",roomBindings="RoomBindings_",SensorBindings="SensorBindings_",object="ObjectData_",people="PeopleData_",roomData="RoomData_";
	public static void CreateTables()
	{
		 String drop="DROP  TABLE IF EXISTS "+data+" , "+sensor+" , "+roomBindings+" , "+SensorBindings+" , "+object+" , "+people+" , "+roomData;
		 
		 String dataTable= "CREATE TABLE "+data+"(regDate DateTime,sensor_ID VARCHAR(100),value FLOAT)";
		
		 String sensorTable= "CREATE TABLE "+sensor+" (sensor_ID VARCHAR(100),sensor_Type VARCHAR(100))"; // done
		 String roomBindingTable= "CREATE TABLE "+roomBindings+" (room_ID INT,sensor_ID VARCHAR(100))"; //done
		 String roomDataTable= "CREATE TABLE "+roomData+" (room_ID INT,room_name VARCHAR(100),height INT,length INT,width INT,room_type VARCHAR(100))";//done
		 String SensorBindingTabler= "CREATE TABLE "+SensorBindings+" (sensor_ID VARCHAR(100),object_ID INT)";	//done
		 String roomObjectTable= "CREATE TABLE "+object+" (room_ID INT,object_ID INT,object_type VARCHAR(100),consumption_type VARCHAR(100),average_consumption INT,object_name VARCHAR(100))";//done
		 String peopleTable="CREATE TABLE "+people+" (first_name VARCHAR(100),last_name  VARCHAR(100),age INT,RFID_ID varchar(100))";//done
		 Statement st;
		try {
			st = conn.createStatement();
			st.execute(drop);
			st.executeUpdate(dataTable);
			st.executeUpdate(roomBindingTable);
			st.executeUpdate(sensorTable);
			st.executeUpdate(roomDataTable);
			st.executeUpdate(SensorBindingTabler);
			st.executeUpdate(roomObjectTable);
			st.executeUpdate(peopleTable);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void insertMainData()
	{
		Statement st=null;
		try {
			st = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		/**
		 * Insert person information into table <people> & rfid sensors into table <sensor>
		 */
		for(int i=0;i<PersonAchive.getCount();i++)
		{
			Person p=PersonAchive.getPerson(i);
			String insertPeople= "INSERT INTO "+people+" (first_name ,last_name ,age ,RFID_ID)	VALUES ('"+p.getName()+"','"+p.getLastName()+"',"+p.getAge()+",'"+p.getrFid().getSensorName()+"')";
			String insertRfSensor = "INSERT INTO " + sensor
			+ " (sensor_ID,sensor_Type)" + "	VALUES ('"
			+ p.getrFid().getSensorName() + "','" + p.getrFid().getType() + "')";
			try {
				st.execute(insertPeople);
				st.execute(insertRfSensor);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		/**
		 * insert room information into table <roomData>
		 */
		for(int i=0;i<House.getInstance().getRoomCount();i++)
		{
			Room r=House.getInstance().getRoom(i);
			String insertRoom="INSERT INTO "+roomData+" (room_ID ,room_name ,height ,length ,width ,room_type)" +
					"	VALUES ("+r.getID()+",'"+r.getName()+"',"	+r.getHeight()+","+r.getLength()+","+r.getWidth()+",'"+r.getType()+"')";
			try {
				st.execute(insertRoom);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			/**
			 * Insert sensor information into table <sensorBindingTable> &
			 * <sensorTable>
			 */
			Set<String> ks = r.getSensors().keySet();
			Iterator<String> it = ks.iterator();
			while (it.hasNext()) {
				Sensor s = r.getSensors().get(it.next());
				String insertSensor = "INSERT INTO " + sensor
						+ " (sensor_ID,sensor_Type)" + "	VALUES ('"
						+ s.getSensorName() + "','" + s.getType() + "')";
				
				String insertIDs= "INSERT INTO " + roomBindings
				+ " (room_ID,sensor_ID)" + "	VALUES ("
				+ r.getID() + ",'" + s.getSensorName() + "')";
				try {
					st.execute(insertSensor);
					st.execute(insertIDs);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			

				 ks = r.getItemList().keySet();
				it = ks.iterator();
				/**
				 * Insert object information into table <SensorBindings> &
				 * <object>
				 */
				while (it.hasNext()) {
					HouseHoldObject o=r.getItem(it.next());
					String insertSensorBindings = "INSERT INTO " + SensorBindings
							+ " (sensor_ID,object_ID)" + "	VALUES ('"
							+ o.getSensor().getSensorName() + "'," + o.getID() + ")";			
					
					String insertObject= "INSERT INTO " + object
					+"(room_ID ,object_ID ,object_type ,consumption_type ,average_consumption ,object_name )	" +
							"VALUES ("
					+ r.getID() + "," + o.getID() + ",'" + o.getType() +"','" + o.getConsumptionType() +"'," + o.getConsumption() +",'" + o.getName() +"')";
					try {
						st.execute(insertSensorBindings);
						st.execute(insertObject);
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
		}

	}
	private static Statement st=null;

	public static void readyInserts()
	{
		try {
			st = conn.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}}
	public static void closeInserts()
	{
		try {
			st.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void insertData(String time,double value,String sensorID){
		String insert=" INSERT INTO " + data
		+ " (regDate,sensor_ID,value)" + "	VALUES ('"
		+ time+ "','" + sensorID + "',"+value+")";
		System.out.println(insert);
		try {
			st.execute(insert);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String args[])
	{
		LoadFamilyXML.loadXML(new File("somefamily2.xml"));
		LoadHouseXML.loadXML(new File("somehouse2.xml"));
		initConnection();
		CreateTables();
		insertMainData();
		readyInserts();
		String d="2011-12-23 22:33:33";
		SimpleDateFormat sdf=new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
		//Date d=sdf.parse(source);		
		insertData(d,22.33, "rfidxx");
		try {
			st.executeQuery("SELECT Hour(regDate) from "+data );
			ResultSet rs=st.getResultSet();
			while(rs.next())
			{
				String s=rs.getString(1);
				
				System.out.println(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}